import pandas as pd
from pypinyin import lazy_pinyin, Style

# Load the Excel file
file_path = 'C:\\Users\\lius\\Desktop\\症状.xlsx'  # Replace with your input file path
df = pd.read_excel(file_path)

# Add new columns
#s_id从100001递增
df['s_id'] = range(100001, 100001 + len(df))
#s_number从200001递增
df['s_number'] = range(200001, 200001 + len(df))
#s_direction=0时s_type=0,否则s_type=1
df['s_type'] = df['s_direction'].apply(lambda x: 0 if x == 0 else 1)
#s_popname_py=s_popname拼音首字母大写
df['s_popname_py'] = df['s_popname'].apply(
    lambda x: ''.join(lazy_pinyin(x, style=Style.FIRST_LETTER)).upper()
)

# Save the modified DataFrame to a new Excel file
output_file_path = 'output.xlsx'  # Replace with your desired output file path
df.to_excel(output_file_path, index=False)